<?php

namespace app\models;

/**
 * 授信
 * Class Credit
 * @package app\models
 */
class Credit
{
    /**
     * 获取某机构的授信数量
     * @param string $institutionCode
     * @param int $status
     * @return false|null|string
     */
    public static function getCount($institutionCode, $status)
    {
        $sql = 'SELECT COUNT(1) FROM v_credit WHERE 1=1';
        if ($institutionCode != '0') {
            $sql = $sql . ' AND institution_code=:institutionCode';
        }
        if ($status != '0') {
            $sql = $sql . ' AND status=:status';
        }
        $cmd = \Yii::$app->db->createCommand($sql);
        if ($institutionCode != '0') {
            $cmd->bindValue(':institutionCode', $institutionCode);
        }
        if ($status != '0') {
            $cmd->bindValue(':status', $status);
        }
        $count = $cmd->queryScalar();
        return $count;
    }

    /**
     * 获取某机构的直属机构所有的授信数量
     * @param string $institutionCode
     * @param int $status
     * @return false|null|string
     */
    public static function getCountWithChildren($institutionCode, $status)
    {
        $sql = 'SELECT COUNT(1) FROM v_credit v0
                        LEFT JOIN t_institution t1 ON t1.`code`=v0.institution_code
                        WHERE t1.parent_code=:institutionCode';
        if ($status != '0') {
            $sql = $sql . ' AND status=:status';
        }
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institutionCode', $institutionCode);
        if ($status != '0') {
            $cmd->bindValue(':status', $status);
        }
        $count = $cmd->queryScalar();
        return $count;
    }

    /**
     *  * 获取某机构授信列表
     * @param int $offset 分页查询起始位置
     * @param int $limit 分页查询每页数量
     * @param string $institutionCode
     * @param int $status
     * @return array
     */
    public static function getList($offset, $limit, $institutionCode, $status)
    {
        $filter = '';
        if ($institutionCode != '0') {
            $filter = $filter . ' AND institution_code=:institutionCode';
        }
        if ($status != '0') {
            $filter = $filter . ' AND status=:status';
        }
        $sql = "SELECT * FROM v_credit WHERE 1=1 $filter ORDER BY create_datetime DESC LIMIT :offset, :limit";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':offset', $offset);
        $cmd->bindValue(':limit', $limit);
        if ($institutionCode != '0') {
            $cmd->bindValue(':institutionCode', $institutionCode);
        }
        if ($status != '0') {
            $cmd->bindValue(':status', $status);
        }

        $items = $cmd->queryAll();

        return $items;
    }

    /**
     * 获取某机构的直属机构所有的授信列表
     * @param $offset
     * @param $limit
     * @param $institutionCode
     * @param $status
     * @return array
     */
    public static function getListWithChildren($offset, $limit, $institutionCode, $status)
    {
        $filter = '';
        if ($status != '0') {
            $filter = $filter . ' AND status=:status';
        }
        $sql = "SELECT v0.* FROM v_credit v0
                        LEFT JOIN t_institution t1 ON t1.`code`=v0.institution_code
                        WHERE t1.parent_code=:institutionCode $filter ORDER BY create_datetime DESC LIMIT :offset, :limit";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':offset', $offset);
        $cmd->bindValue(':limit', $limit);
        $cmd->bindValue(':institutionCode', $institutionCode);
        if ($status != '0') {
            $cmd->bindValue(':status', $status);
        }

        $items = $cmd->queryAll();
        return $items;
    }

    /**
     * 获取授信详情
     * @param int $orderId
     * @return array|false
     */
    public static function getDetail($creditId)
    {
        $cmd = \Yii::$app->db->createCommand('SELECT * FROM v_credit WHERE id=:creditId');
        $cmd->bindValue(':creditId', $creditId);
        $item = $cmd->queryOne();

        return $item;
    }

    /**
     * 新增授信
     * @param mixed $credit
     * @return int
     * @throws \yii\db\Exception
     */
    public static function add($credit)
    {
        $cmd = \Yii::$app->db->createCommand('call p_add_credit(:in_institution_code, :in_credit_quota, :in_apply_user_name, :in_remark)');
        $cmd->bindValue(':in_institution_code', $credit['institution_code']);
        $cmd->bindValue(':in_credit_quota', $credit['credit_quota']);
        $cmd->bindValue(':in_apply_user_name', $credit['apply_user_name']);
        $cmd->bindValue(':in_remark', $credit['remark']);
        return $cmd->execute();
    }

    /**
     * 授信审核
     * @param mixed $data
     * @return int
     * @throws \yii\db\Exception
     */
    public static function approve($data)
    {
        $status = 1;

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            $cmd = \Yii::$app->db->createCommand('call p_approve_credit(:in_credit_id, :in_credit_status)');
            $cmd->bindValue(':in_credit_id', $data['credit_id']);
            $cmd->bindValue(':in_credit_status', $data['credit_status']);
            $status = $cmd->queryScalar();

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status;
    }

    /**
     * 根据机构编码获取授信
     * @param $institution_code
     * @return array|false|null
     */
    public static function getCreditByInstitutionCode($institution_code)
    {
        $sql = 'SELECT * FROM t_institution_account WHERE institution_code = :institution_code';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $result = $cmd->queryOne();
        if ($result == false) {
            return null;
        }
        return $result;
    }

    /**
     * 获取授信合计
     * @return array|false|null
     */
    public static function getCreditSum()
    {
        $sql = 'SELECT sum(balance) as balance,sum(credit_quota) as credit_quota FROM t_institution_account';
        $cmd = \Yii::$app->db->createCommand($sql);
        $result = $cmd->queryOne();
        if ($result == false) {
            return null;
        }
        return $result;
    }
}